package com.yict.csms.resourceplan.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import javax.sql.DataSource;

import org.hibernate.Query;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;
import org.springframework.stereotype.Repository;

import com.yict.common.dao.impl.CommonDao;
import com.yict.common.entity.PageEntity;
import com.yict.csms.resourceplan.dao.IShipManagerDaoImpl;
import com.yict.csms.resourceplan.entity.ShipCsms;
import com.yict.csms.resourceplan.entity.ShipNGen;

@Repository
public class ShipManagerDaoImpl extends CommonDao<ShipCsms, Long> implements
		IShipManagerDaoImpl {

	private PageEntity pageNGen;
	public DataSource getNGenDataSource(){
		ApplicationContext context = new FileSystemXmlApplicationContext(
		"classpath*:com/yict/csms/resources/applicationContext.xml");
   DataSource nGenDataSource = (DataSource) context.getBean("nGenDataSource");
   return nGenDataSource;
	}
	
	public List<ShipNGen> searchNGen(String queryString,
			Map<String, Object> valueMap, PageEntity page) throws SQLException {
		List<ShipNGen> list = new ArrayList<ShipNGen>();
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			DataSource nGenDataSource = getNGenDataSource();
			conn = nGenDataSource.getConnection();
			stmt = conn.prepareStatement(queryString,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			Set<Map.Entry<String, Object>> entrys = valueMap.entrySet();
			int j = 1;
			for (Entry<String, Object> entry : entrys) {
				String key = entry.getKey();
				Object value = entry.getValue();
				if(value!=null&&!"".equals(value)){
					if(key.equals("etbDate1")||key.equals("etbDate2")){
						stmt.setString(j, (String)value);
					}else{
				   stmt.setString(j, "%"+value+"%");
					}
				j++;
				}
			}
			rs = stmt.executeQuery();
			rs.last();
			pageNGen = new PageEntity();
			pageNGen.setTotalRecord(rs.getRow());
			int row = (page.getToPage()-1)*page.getPageSize()+1;
			rs.absolute(row); 
			int i = 0;
			while (rs.next()&&i<page.getPageSize()) {
				ShipNGen ship = new ShipNGen();
//				ship.setShipId(rs.getLong("SHIPID"));
				ship.setShipName(rs.getString("SHIPNAME"));
				ship.setVoyage(rs.getString("VOYAGE"));
//				ship.setSource(rs.getString("SOURCE"));
				ship.setShipType(rs.getString("SHIPTYPE"));
				ship.setQcList(rs.getString("QCLIST"));
//				ship.setWorkerGroup1(rs.getDouble("WORKERGROUP1"));
//				ship.setWorkerGroupType1(rs.getString("WORKERGROUPTYPE1"));
//				ship.setWorkerGroup2(rs.getDouble("WORKERGROUP2"));
//				ship.setWorkerGroupType2(rs.getString("WORKERGROUPTYPE2"));
//				ship.setShipOption(rs.getString("SHIPOPTION"));
//				ship.setCarQuan(rs.getDouble("CARQUAN"));
				ship.setBerthId(rs.getString("BERTHID"));
				ship.setEtb(rs.getDate("ETB"));
				ship.setEtd(rs.getDate("ETD"));
				ship.setEtdStutas(rs.getString("ETDSTUTAS"));
				ship.setTcc(rs.getDate("TCC"));
				ship.setDs(rs.getLong("DS"));
				ship.setDsn(rs.getLong("DSN"));
				ship.setDsw(rs.getLong("DSW"));
				ship.setLd(rs.getLong("LD"));
				ship.setLdn(rs.getLong("LDN"));
				ship.setLdw(rs.getLong("LDW"));
				ship.setRs(rs.getLong("RS"));
				ship.setSh(rs.getLong("SH"));
				ship.setHc(rs.getLong("HC"));
//				ship.setStatus(rs.getString("STATUS"));
//				ship.setIsAttendCar(rs.getString("ISATTENDCAR"));
//				ship.setIsBilling(rs.getString("ISBILLING"));
//				ship.setRemarks(rs.getString("REMARKS"));
//				ship.setCreateperson(rs.getLong("CREATEPERSON"));
//				ship.setCreatedate(rs.getDate("CREATEDATE"));
//				ship.setUpdateperson(rs.getLong("UPDATEPERSON"));
//				ship.setUpdatedate(rs.getDate("UPDATEDATE"));
				list.add(ship);
				i++;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
			if (conn != null)
				conn.close();
		}
		return list;
	}

	public LinkedHashMap<String, String> searchCompany(String queryString,String companyIds) throws SQLException{
		LinkedHashMap<String, String>  map = new LinkedHashMap<String, String>();
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			DataSource nGenDataSource = getNGenDataSource();
			rs = stmt.executeQuery();
			conn = nGenDataSource.getConnection();
			stmt = conn.prepareStatement(queryString,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			stmt.setString(1, companyIds);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
			if (conn != null)
				conn.close();
		}
		return map;
	}
	
	public boolean verifyShipName(String shipId,String shipName, String voyage) {
		boolean bool = false;
		String queryString = "Select count(*) from ShipCsms as c where lower(c.shipName) ='"
				+ (shipName == null ? null : shipName.toLowerCase())
				+ "' and lower(c.voyage) ='" + (voyage == null ? null : voyage.toLowerCase()) + "'";
		try {
			Query query = this.getSession().createQuery(queryString);
			Long i = (Long) query.uniqueResult();
			if (shipId==null&&i == 0){
				bool = true;
			}else if (shipId!=null&&i == 1){
				bool = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return bool;
	}

	public ShipCsms findbyId(Long id) {
		ShipCsms t = null;
		try {
			Object obj = this.getTemplate().get(ShipCsms.class, id);
			t = (ShipCsms) obj;
		} catch (Exception e) {
			logger.error(e);
		}
		return t;
	}
	
	public PageEntity getPageNGen() {
		return pageNGen;
	}

	public void setPageNGen(PageEntity pageNGen) {
		this.pageNGen = pageNGen;
	}
}
